/*
 * Copyright (C) 2007 James Deng (jtdeng@gmail.com)
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */


/*
 * A error logging package 
 *
 * CRITICAL 50, ERROR 40, WARNING 30, INFO 20, DEBUG 10 
 */

whenever sqlerror exit sql.sqlcode

prompt 'Creating package utl_logging...'

create table logging_msg (	log_id number primary key,
							session_id number,
							time timestamp default systimestamp not null, 
							level_id number not null,
							module varchar2(256),
							msg varchar2(4000) );

create table logging_level (level_id number primary key,
							level_name varchar2(10) );

insert into logging_level values (10, 'DEBUG');
insert into logging_level values (20, 'INFO');
insert into logging_level values (30, 'WARNING');
insert into logging_level values (40, 'ERROR');
insert into logging_level values (50, 'CRITICAL');
commit;


create or replace view logging_view as
select m.log_id, m.session_id, m.time, l.level_name, m.module, m.msg
from logging_msg m, logging_level l
where m.level_id=l.level_id
order by m.log_id asc;

CREATE SEQUENCE SEQ_LOG_ID
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 999999999999999999999999999
NOCACHE 
CYCLE 
ORDER;

create or replace package utl_logging
as	
	CRITICAL_LEVEL	number := 50;
	ERROR_LEVEL		number := 40;
	WARNING_LEVEL	number := 30;
	INFO_LEVEL		number := 20;
	DEBUG_LEVEL		number := 10;
	
	procedure set_logging_level(lid IN number);
	
	/*used to distinguish logs generated by different sessions*/
	procedure set_session_id(sid IN number);
	
	procedure critical(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type);
						
	procedure error(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type);
						
	procedure warning(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type);
						
	procedure info(		module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type);
						
	procedure debug(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type);
	
	
end;
/
show err;

create or replace package body utl_logging
as
	m_current_logging_level_id number := WARNING_LEVEL;
	m_session_id number := 0;
	
	procedure set_logging_level( lid IN number)
	is
	begin
		m_current_logging_level_id := lid;
	end;

	procedure set_session_id( sid IN number)
	is
	begin
		m_session_id := sid;
	end;
	
	procedure log(	level_id	IN logging_msg.level_id%type, 
					module		IN logging_msg.module%type,
					msg			IN logging_msg.msg%type )
	is
		pragma autonomous_transaction;
	begin
		if level_id	>= m_current_logging_level_id then
			insert into logging_msg values(	seq_log_id.nextval, m_session_id, sysdate, level_id, module, msg );
			commit;
		end if;
	end;
	
	procedure critical(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type)
	is
	begin
		log(CRITICAL_LEVEL, module, msg);	
	end;					
						
	procedure error(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type)
	is
	begin
		log(ERROR_LEVEL, module, msg);
	end;
							
	procedure warning(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type)
	is
	begin
		log(WARNING_LEVEL, module, msg);	
	end;
							
	procedure info(		module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type)
	is
	begin
		log(INFO_LEVEL, module, msg);
	end;
							
	procedure debug(	module IN logging_msg.module%type,
						msg    IN logging_msg.msg%type)
	is
	begin
		log(DEBUG_LEVEL, module, msg);
	end;
							
end;
/
show err;



/*a small test case*/

--begin
--	utl_logging.set_logging_level(UTL_LOGGING.DEBUG_LEVEL);
--	utl_logging.set_session_id(0);
--	utl_logging.critical('main', 'a critical error happens');
--	utl_logging.error('main', 'error level');
--	utl_logging.warning('main', 'warning level');
--	utl_logging.info('main', 'info level');
--	utl_logging.debug('main', 'debug level');
--end;
--/